package implementor;

import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import daoInterface.ModelDao;
import dbhandeling.Connections;
import dto.Material;
import model.CustomerReportItems;
import model.SalesReportItems;
import model.Stock;



public class ModelImp implements ModelDao {
	
	@Override
	public SalesReportItems[] retriveAccountReports(Date fromDate,
			Date toDate, String category, String sellerName) {
		
		if(category.equalsIgnoreCase("All")||category.contains("Select"))
			category="%";
		if(sellerName.equalsIgnoreCase("All")||sellerName.contains("Select"))
			sellerName="%";
		
		
		String baseSql= "	SELECT SUM(SELL.SELLMRP),SUM(SELL.SELLQUANT)AS TOTALSELL , \n"+
						"	SELL.MCODE,SELLERINFO.SELLERNAME,PURCHASE.PURCHASEDATE  ,MATERIAL.MQUANT, \n"+
						"	PURCHASE.PURCHASEQUANT,PURCHASE.PURCHASEPRICE,CATEGORY.LEAFNAME \n"+
						"	FROM MATERIAL JOIN PURCHASE ON MATERIAL.PURCHASEID = PURCHASE.AUTOPURCHASEID \n"+
						"	JOIN CATEGORY ON CATEGORY.AUTOID = MATERIAL.ID \n"+
						"	JOIN SELLERINFO ON PURCHASE.SELLERID = SELLERINFO.AUTOSELLERID \n"+ 
						"	JOIN SELL ON SELL.MCODE = MATERIAL.MCODE \n "+   
			 
						"    WHERE BILL.BILLDATE BETWEEN "+"'" + fromDate + "'" + " \n" +
						"    AND " + "'" + toDate + "'" + " \n"+
						"    AND CATEGORY.LEAFNAME LIKE "+ "'" + category + "'" + " \n"+ 
						"    AND SELLERINFO.SELLERNAME LIKE "+ "'" + sellerName +"'" + " \n"+
						"    GROUP BY SELLERID,PURCHASEDATE,MATERIAL.MCODE,PURCHASEQUANT,PURCHASEPRICE,CATEGORY.LEAFNAME";
		
		
		
         String sql=baseSql;
		Statement st =Connections.getMyStatement();
		SalesReportItems[] accountReportItems=null;
		 
		try {
	
			ResultSet rs= st.executeQuery(sql);
			int count=0;
			while(rs.next())
				count++;
			accountReportItems = new SalesReportItems[count];
			rs= st.executeQuery(sql);
			int i=0;
			while(rs.next())
			{
				 accountReportItems[i]=new SalesReportItems();
			     accountReportItems[i].setmCode(rs.getString("MCODE"));
			     accountReportItems[i].setSellerName(rs.getString("SELLERNAME"));
			     accountReportItems[i].setPurchasePrice(rs.getDouble("PURCHASEPRICE"));
			     accountReportItems[i].setPurchaseDate(rs.getDate("PURCHASEDATE"));
			     accountReportItems[i].setPurchaseQuant(rs.getInt("PURCHASEQUANT"));
			     accountReportItems[i].setRemainingQuant(rs.getInt("mQuant"));
			     accountReportItems[i].setCatName(rs.getString("LEAFNAME"));  
			     accountReportItems[i].setSellAmt(rs.getDouble("TOTALSELL"));
			     accountReportItems[i].setPurchaseAmt(accountReportItems[i].getPurchasePrice()*accountReportItems[i].getPurchaseQuant());
			     i++;		
			}		
		} catch (SQLException e) {
			// TODO Auto-generated catch block
		    System.out.println(sql);
			e.printStackTrace();
		}
		return accountReportItems;
	}

	@Override
	public CustomerReportItems[] retreiveCustomerReport(Date from, Date to) {
		
		String sql=   "	SELECT SUM(SELL.SELLMRP) AS AMOUNT FROM CUSTOMER JOIN BILL ON CUSTOMER.AUTOCUSTOMERID = BILL.CUSTOMERID+\n"+
					  "	JOIN SELL ON SELL.BILLID = BILL.AUTOIDBILL +\n"+
		              " GROUP BY BILL.AUTOIDBILL,CUSTOMER.AUTOCUSTOMERID,CUSTOMERNAME,CUSTOMERPHONE,BILLDATE "+
		              "	WHERE BILL.BILLDATE between "+"'"+from+"'" + " AND " +"'"+to+"'";
					
		Statement st = Connections.getMyStatement();
		CustomerReportItems[] customerReportItems=null;
		 
		try {
	
			ResultSet rs= st.executeQuery(sql);
			int count=0;
			while(rs.next())
				count++;
			customerReportItems = new CustomerReportItems[count];
			rs= st.executeQuery(sql);
			int i=0;
			while(rs.next())
			{
				customerReportItems [i]=new CustomerReportItems();
				customerReportItems[i].setCustomerName(rs.getString("CUSTOMERNAME"));
				customerReportItems[i].setCustomerPhone(rs.getString("CUSTOMERPHONE"));
				customerReportItems[i].setPurchaseDate(rs.getDate("PURCHASEDATE"));
				customerReportItems[i].setPurchaseAmt(rs.getDouble("AMOUNT"));
				
				i++;
			
			}
			
			
			
		} catch (SQLException e) {
			
			e.printStackTrace();
		}
		return customerReportItems;
		
		
	}

	@Override
	public Stock[] retreiveStockReport(String category, String size, String sellerName) {
		
		
		
		
		
		if(category.equals("All")==true || category.contains("Select")==true)
			
		{
			category="%";
		}
		
		if(sellerName.equals("All")==true || sellerName.contains("Select")==true)
			
		{
			sellerName="%";
		}
		
		if(size.equals("All")==true || size.contains("Select")==true)
			
		{
			size="%";
		}
		String sql="SELECT * FROM MATERIAL JOIN PURCHASE ON PURCHASE.AUTOPURCHASEID = MATERIAL.PURCHASEID\n"+ 
		"JOIN SELLERINFO ON PURCHASE.SELLERID = SELLERINFO.AUTOSELLERID\n"+
		" JOIN CATEGORY ON CATEGORY.AUTOID = MATERIAL.ID\n"+
		" JOIN SIZE ON SIZE.AUTOSIZECODE = MATERIAL.SIZEID\n"+
		" WHERE CATEGORY.LEAFNAME LIKE "+ "'" + category + "'" + 
		" AND SIZE.SIZENAME LIKE " + "'" + size + "'" + 
		" AND SELLERINFO.SELLERNAME LIKE "+ "'" + sellerName +"'" ;
		
		Statement s=Connections.getMyStatement();
		Stock[] stockItems=null;
		try {
			ResultSet rs=s.executeQuery(sql);
			int count=0;
			while(rs.next())
				count++;
			stockItems=new Stock[count];
			rs=s.executeQuery(sql);
			int i=0;
			while(rs.next())
			{
				stockItems[i]=new Stock();
				stockItems[i].setSellerName(rs.getString("SELLERNAME"));
				Material matmodel=new Material();	
				matmodel.setCategoryname(rs.getString("leafNAME"));
				matmodel.setSizeName(rs.getString("SIZENAME"));
				
				matmodel.setmQuant(rs.getInt("MQUANT"));
				stockItems[i].setMatmodel(matmodel);
				stockItems[i].setPurchaseQuant(rs.getDouble("PURCHASEQUANT"));
				i++;
			}
		} catch (SQLException e) {
			
			System.out.println(sql);
			e.printStackTrace();
		}
		return stockItems;
	}
	

}
